﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ModelLayer;

namespace FremtidensPlejehjem_Server.Database
{
    public static class DbActivity
    {
        /// <summary>
        /// Creates an activity, based on the input parameters.
        /// </summary>
        /// <param name="activityName">Specifies the name of the activity to be created.</param>
        /// <param name="activityDescription">Specifies the description of the activity to be created.</param>
        /// <param name="roomNumber">Specifies the number of the room to be added to the activity.</param>
        /// <param name="activityStartDate">Specifies the start date of the activity.</param>
        /// <param name="activityEndDate">Specifies the end date of the activity.</param>
        public static void CreateActivity(string activityName, string activityDescription, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "insert into Activity (activityName, activityDescription) values (@activityName, @activityDescription) " +
                           "insert into Activity_Room (activityID, roomNumber, startDate, endDate) values(@actvityId, @roomNumber, @activityStartDate, @activityEndDate)";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityName", activityName);
                    cmd.Parameters.AddWithValue("@activityDescription", activityDescription);
                    cmd.Parameters.AddWithValue("@activityId", DbGeneral.GetNextId("activityID", "Activity"));
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Gets an activity, based on the activityId parameter.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity to be retrieved.</param>
        /// <returns>Returns an Activity object.</returns>
        public static Activity GetActivityById(int activityId)
        {
            string query = "select ac.activityName, ac.activityDescription " +
                           "from Activity ac " +
                           "where ac.activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    Activity activity = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        activity = new Activity();

                        activity.Name = dr["activityName"].ToString();
                        activity.Description = dr["activityDescription"].ToString();

                        activity.ActivityRooms = DbActivity.GetActivityRoomsByActivityId(activityId);
                    }

                    conn.Close();

                    return activity;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Gets a list of Activity Rooms, based on the activityId parameter.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity to be used.</param>
        /// <returns>Returns a List of Activity objects.</returns>
        public static List<ActivityRoom> GetActivityRoomsByActivityId(int activityId)
        {
            string query = "select ar.activityID, ar.roomNumber, ar.startDate, ar.endDate " +
                           "from Activity_Room ar " +
                           "where ar.activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<ActivityRoom> activityRooms = new List<ActivityRoom>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        ActivityRoom activityRoom = new ActivityRoom();

                        activityRoom.StartDate = Convert.ToDateTime(dr["startDate"]);
                        activityRoom.EndDate = Convert.ToDateTime(dr["endDate"]);

                        activityRoom.Room = DbRoom.GetRoomByActivityRoom(Convert.ToInt32(dr["activityID"]), Convert.ToInt32(dr["roomNumber"]));

                        activityRooms.Add(activityRoom);
                    }

                    conn.Close();

                    return activityRooms;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Gets a list of Activities, based on the roomNumber parameter.
        /// </summary>
        /// <param name="roomNumber">Specifies the number of the room to be used.</param>
        /// <returns>Returns a List of Activity  objects.</returns>
        public static List<Activity> GetActivitiesByRoom(int roomNumber)
        {
            string query = "select ac.activityID, ac.activityName, ac.activityDescription " +
                           "from Activity ac, Activity_Room ar " +
                           "where ar.roomNumber = @roomNumber" +
                           "and ac.activityID = ar.activityID ";


            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Activity> activities = null;

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Activity activity = new Activity();

                        activity.Name = dr["activityName"].ToString();
                        activity.Description = dr["activityDescription"].ToString();

                        activity.ActivityRooms = GetActivityRoomsByActivityId(Convert.ToInt32(dr["activityID"]));
                    }

                    conn.Close();

                    return activities;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Gets a list of all activities.
        /// </summary>
        /// <returns>Returns a List of Activity objects.</returns>
        public static List<Activity> GetAllActivities()
        {
            string query = "select ac.activityID, ac.activityName, ac.activityDescription " +
                           "from Activity ac";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Activity> activities = new List<Activity>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Activity activity = new Activity();

                        activity.Name = dr["activityName"].ToString();
                        activity.Description = dr["activityDescription"].ToString();

                        activity.ActivityRooms = GetActivityRoomsByActivityId(Convert.ToInt32(dr["activityID"]));

                        activities.Add(activity);
                    }

                    conn.Close();

                    return activities;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Gets the 5 next activities.
        /// </summary>
        /// <returns>Returns a List of Activity objects.</returns>
        public static List<Activity> Get5NextActivities()
        {
            string query = "select top 5 ac.activityID, ac.activityName, ac.activityDescription " +
                           "from Activity ac, Activity_Room ar " +
                           "where ar.activityID = ac.activityID " +
                           "and ar.startDate > CURRENT_TIMESTAMP " +
                           "order by ar.startDate asc";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    List<Activity> activities = new List<Activity>();

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    conn.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Activity activity = new Activity();

                        activity.Name = dr["activityName"].ToString();
                        activity.Description = dr["activityDescription"].ToString();

                        activity.ActivityRooms = GetActivityRoomsByActivityId(Convert.ToInt32(dr["activityID"]));

                        activities.Add(activity);
                    }

                    conn.Close();

                    return activities;
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Updates an activity, based on the input paramters.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity to be updated.</param>
        /// <param name="activityName">Specifies the name to be used in the update.</param>
        /// <param name="activityDescription">Specifies the description to be used in the update.</param>
        public static void UpdateActivity(int activityId, string activityName, string activityDescription)
        {
            string query = "update Activity set activityName = @activityName, activityDescription = @activityDescription where activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@activityName", activityName);
                    cmd.Parameters.AddWithValue("@activityDescription", activityDescription);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Updates an activity room, based on the input parameters.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity from the activity room.</param>
        /// <param name="roomNumber">Specifies the number of the room from the activity room.</param>
        /// <param name="activityStartDate">Specifies the start date to be used in the update.</param>
        /// <param name="activityEndDate">Specifies the end date to be used in the update.</param>
        public static void UpdateActivityRoom(int activityId, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "update Activity_Room set roomNumber = @roomNumber, activityStartDate = @activityStartDate, activityEndDate = @activityEndDate where activityID = @activityId ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Inserts an activity room, based on the input parameters.
        /// </summary>
        /// <param name="activityId">Specifies the ID of the activity to be used in the insert.</param>
        /// <param name="roomNumber">Specifies the number of the room to be used in the insert.</param>
        /// <param name="activityStartDate">Specifies the start date to be used in the insert.</param>
        /// <param name="activityEndDate">Specifies the end date to be used in the insert.</param>
        public static void InsertAdditionalActivityRoom(int activityId, int roomNumber, string activityStartDate, string activityEndDate)
        {
            string query = "insert into Activity_Room (activityID, roomNumber, startDate, endDate) values(@actvityId, @roomNumber, @activityStartDate, @activityEndDate ";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);
                    cmd.Parameters.AddWithValue("@activityStartDate", activityStartDate);
                    cmd.Parameters.AddWithValue("@activityEndDate", activityEndDate);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Deletes an activity, based on the activityId parameter.
        /// </summary>
        /// <param name="activityId"></param>
        public static void DeleteActivity(int activityId)
        {
            string query = "delete from Activity_Room where activityID = @activityId " +
                           "delete from Activity where activityID = @activityId";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }

        /// <summary>
        /// Removes an activity room from an activity, based on the activityId and roomNumber parameters.
        /// </summary>
        /// <param name="activityId">Specifies the ID from the activity room.</param>
        /// <param name="roomNumber">Specifies the number of the room from the activity room.</param>
        public static void RemoveActivityRoomFromActivity(int activityId, int roomNumber)
        {
            string query = "delete from Activity_Room where activityID = @activityId and roomNumber = @roomNumber";

            using (SqlConnection conn = new SqlConnection(DbGeneral.GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@activityId", activityId);
                    cmd.Parameters.AddWithValue("@roomNumber", roomNumber);

                    conn.Open();

                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
                catch (SqlException e)
                {
                    //Insert Logging Here
                    throw;
                }
            }
        }
    }
}
